Correlated and Non-Correlated Subqueries in MySQL

Course: MySQL Training

Topic: Correlated and Non-Correlated Subqueries

Course Link: MySQL Training Course

Demo Link: Register for a Course Demo

1. Introduction to Subqueries in MySQL

A subquery is an SQL query written inside another SQL statement. It is also called an inner query or nested query. The query containing the subquery is known as the outer query. Subqueries allow developers to retrieve data based on the result of another query, compare values with calculated results, filter records, and solve complex database problems.

In MySQL, subqueries can be used with statements such as SELECT, INSERT, UPDATE, and DELETE. They can also appear in clauses such as WHERE, HAVING, and FROM, subject to MySQL's syntax and query restrictions.

Based on how the inner query relates to the outer query, subqueries are commonly classified into two types:

• Non-Correlated Subquery: The inner query does not depend on values from the outer query and can generally be executed independently.
• Correlated Subquery: The inner query references one or more columns from the outer query and is logically evaluated in relation to the current outer-row values.

Understanding the difference between these two types helps developers write correct SQL queries, analyze query execution, and select appropriate approaches for data retrieval.

2. Learning Objectives

After completing this topic, learners should be able to:

• Understand the meaning and purpose of subqueries in MySQL.
• Identify inner queries and outer queries in SQL statements.
• Explain correlated and non-correlated subqueries.
• Write non-correlated subqueries using comparison operators and aggregate functions.
• Write correlated subqueries using table aliases and outer-query references.
• Use EXISTS and NOT EXISTS with correlated queries.
• Compare subqueries with joins and understand when each approach is appropriate.
• Identify common errors involving aliases, NULL values, and multiple-row results.
• Evaluate query readability and performance using appropriate testing methods.

3. Understanding the Basic Structure of a Subquery

A subquery is generally enclosed in parentheses and placed inside the outer SQL statement.

3.1 General Syntax

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT column_name
    FROM another_table
    WHERE condition
);

In this structure:

• SELECT in the outer query identifies the data to return.
• The outer FROM clause identifies the main table.
• The inner SELECT retrieves a value or set of values used by the outer query.
• The parentheses identify the subquery.
• The operator determines how the outer value is compared with the subquery result.

3.2 Example of a Simple Subquery

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

The inner query calculates the average salary of all employees. The outer query returns employees whose salaries are greater than that average. Because the inner query does not refer to a column from the outer query, this is a non-correlated subquery.

4. Sample Database for Practical Examples

The following examples use two tables: employees and departments. The employee table stores employee information, while the department table stores department details.

4.1 Employees Table

4.2 Departments Table

4.3 Creating the Sample Tables

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    salary DECIMAL(10,2),
    manager_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

INSERT INTO departments
    (department_id, department_name)
VALUES
    (1, 'IT'),
    (2, 'HR'),
    (3, 'Finance'),
    (4, 'Marketing');

INSERT INTO employees
    (employee_id, employee_name, department_id, salary, manager_id)
VALUES
    (101, 'Amit', 1, 50000, NULL),
    (102, 'Priya', 1, 65000, 101),
    (103, 'Rahul', 2, 45000, 101),
    (104, 'Neha', 2, 70000, 103),
    (105, 'Vikram', 3, 60000, 101),
    (106, 'Sana', 1, 55000, 102);

Note: Run the department table creation and insert statements before creating and inserting employee records because the employee table references the department table through a foreign key.

5. Non-Correlated Subqueries in MySQL

5.1 Definition

A non-correlated subquery is an inner query that does not reference columns from the outer query. It can be evaluated independently because its result does not depend on the current row being processed by the outer query.

The outer query uses the result returned by the inner query to filter, compare, or retrieve records.

5.2 General Syntax

SELECT column_name
FROM table_name
WHERE column_name operator (
    SELECT expression
    FROM another_table
    WHERE condition
);

5.3 Example: Employees Earning More Than the Overall Average Salary

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Explanation:

1. The inner query calculates the average salary across all employee records.
2. The inner query does not refer to any outer-query alias.
3. The outer query compares each employee's salary with the calculated average.
4. Only employees whose salary exceeds the average are returned.

For the sample data, the average salary is approximately 57,500. The query returns Priya, Neha, and Vikram because their salaries are greater than that value.

5.4 Example: Employees Working in the IT Department

SELECT employee_name, department_id
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'IT'
);

Explanation: The inner query finds the department ID for IT. The outer query then retrieves employees assigned to that department. The inner query is independent of the outer query.

This example uses the equality operator because the department name is expected to identify a single department. If the inner query returns multiple rows, MySQL raises an error for this scalar comparison. Use IN when multiple matching values are expected.

5.5 Example: Employees Earning the Highest Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

The inner query calculates the maximum salary. The outer query returns all employees earning that salary. In the sample data, Neha is returned with a salary of 70,000.

5.6 Example: Employees Working in Departments With Existing Records

SELECT employee_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('IT', 'HR')
);

The inner query returns the IDs of IT and HR. The outer query returns employees whose department IDs belong to that result set.

5.7 Common Operators Used With Non-Correlated Subqueries

6. Correlated Subqueries in MySQL

6.1 Definition

A correlated subquery is an inner query that references one or more columns from the outer query. Because its result depends on values from the current outer-row context, it cannot be evaluated as a completely independent query in the same way as a non-correlated subquery.

Conceptually, MySQL evaluates the inner query using values supplied by the outer query. The optimizer may transform or optimize the execution plan, so this logical explanation does not mean that the inner query must always be physically executed from scratch for every row.

6.2 General Syntax

SELECT outer_alias.column_name
FROM table_name AS outer_alias
WHERE outer_alias.column_name operator (
    SELECT aggregate_function(inner_alias.column_name)
    FROM table_name AS inner_alias
    WHERE inner_alias.related_column =
          outer_alias.related_column
);

The defining feature is the reference to outer_alias.related_column inside the subquery. That reference connects the inner query to the outer query.

6.3 Example: Employees Earning More Than Their Department's Average Salary

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Explanation:

1. The outer query uses the alias e for the employees table.
2. For the current outer employee, the subquery uses that employee's department ID.
3. The inner query calculates the average salary of employees in the same department.
4. The outer query compares the current employee's salary with that department average.
5. Employees whose salaries exceed their own department's average are returned.

The condition e2.department_id = e.department_id is what makes the subquery correlated. The reference e.department_id comes from the outer query.

For the sample data, the department averages are:

6.4 Example: Employees Earning the Highest Salary in Their Department

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

This query identifies the maximum salary within the department of each outer employee. The outer query returns employees whose salaries match those department-specific maximums.

For the sample data, the result includes Priya from IT, Neha from HR, and Vikram from Finance. If multiple employees share the department's maximum salary, all matching employees are returned.

6.5 Example: Employees Who Have at Least One Colleague in Their Department

SELECT e.employee_id,
       e.employee_name,
       e.department_id
FROM employees AS e
WHERE EXISTS (
    SELECT 1
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
      AND e2.employee_id <> e.employee_id
);

Explanation: For each employee, the subquery checks whether another employee exists in the same department. The condition e2.employee_id <> e.employee_id prevents the employee from matching their own record. Employees who have at least one colleague are returned.

In the sample data, employees in IT and HR meet this condition. Vikram, who is the only employee in Finance, does not.

6.6 Example: Departments With No Employees

SELECT d.department_id,
       d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

The subquery checks whether an employee exists for each department. The outer query returns departments for which no matching employee exists. In the sample data, Marketing is returned.

NOT EXISTS is often a reliable approach for finding unmatched records because its result depends on whether matching rows exist, rather than on the values returned by those rows.

6.7 Example: Employees Earning More Than Their Manager

SELECT e.employee_name,
       e.salary,
       e.manager_id
FROM employees AS e
WHERE e.manager_id IS NOT NULL
  AND e.salary > (
      SELECT m.salary
      FROM employees AS m
      WHERE m.employee_id = e.manager_id
  );

This correlated subquery finds the salary of the current employee's manager and compares it with the employee's salary. The subquery references e.manager_id from the outer query.

In the sample data, Neha earns more than Rahul, her manager. Employees without a manager are excluded by the outer condition. If the manager reference does not match any employee, the scalar subquery returns NULL, and the comparison does not evaluate to true.

7. Key Differences Between Correlated and Non-Correlated Subqueries

8. Step-by-Step Method for Writing a Non-Correlated Subquery

Step 1: Identify the Requirement

Suppose the requirement is to find employees earning more than the company's average salary.

Step 2: Write the Inner Query

SELECT AVG(salary)
FROM employees;

This query calculates the overall average salary without depending on an outer row.

Step 3: Add the Outer Query

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Step 4: Test the Result

Check the calculated average independently and compare each employee's salary against it. This helps verify that the query meets the requirement.

9. Step-by-Step Method for Writing a Correlated Subquery

Step 1: Identify the Related Records

Suppose the requirement is to find employees whose salaries exceed the average salary of their own departments.

Step 2: Assign Table Aliases

Use e for the outer employee and e2 for the employees considered inside the subquery.

Step 3: Write the Correlation Condition

WHERE e2.department_id = e.department_id

This condition restricts the inner calculation to the department associated with the current outer employee.

Step 4: Add the Aggregate Calculation

SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department_id = e.department_id

Step 5: Compare the Outer Row

SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Step 6: Validate the Output

Calculate the average salary for each department separately and confirm that each returned employee earns more than the relevant department average.

10. Using Aggregate Functions in Subqueries

Aggregate functions are frequently used with both correlated and non-correlated subqueries. They summarize values from a set of records and return a calculated result.

10.1 Non-Correlated Example: Highest Salary

SELECT employee_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary)
    FROM employees
);

10.2 Correlated Example: Highest Salary in Each Department

SELECT e.employee_name, e.department_id, e.salary
FROM employees AS e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

The main difference is the grouping context. The first query calculates one maximum for the entire table, while the second calculates a maximum for the department associated with each outer row.

11. Correlated Subqueries With EXISTS and NOT EXISTS

11.1 Understanding EXISTS

EXISTS checks whether the subquery returns at least one row. It evaluates to true when a matching row exists and false when no matching row exists. The values in the subquery's select list are generally unimportant, so SELECT 1 is commonly used.

SELECT d.department_name
FROM departments AS d
WHERE EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

This query returns departments with at least one employee.

11.2 Understanding NOT EXISTS

NOT EXISTS returns true when the subquery returns no rows. It is commonly used to identify missing relationships or unmatched records.

SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

This query returns departments that do not have employees. In the sample data, Marketing is returned.

11.3 EXISTS Compared With IN

-- Using IN
SELECT department_name
FROM departments
WHERE department_id IN (
    SELECT department_id
    FROM employees
);

-- Using EXISTS
SELECT d.department_name
FROM departments AS d
WHERE EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

Both queries identify departments that have employees. The IN form compares department IDs with a set of values. The EXISTS form checks for matching employee records in relation to each department. The optimizer may transform either query, so neither form is automatically faster in every situation.

12. Correlated Subqueries With UPDATE

Subqueries can also be used in data modification statements. A correlated subquery in an UPDATE statement can calculate a value related to the current record.

12.1 Example: Update a Salary Comparison Flag

Suppose a table contains a Boolean column named above_department_average. The following statement sets it to 1 when an employee earns more than the average salary in their department and 0 otherwise.

ALTER TABLE employees
ADD COLUMN above_department_average TINYINT(1) DEFAULT 0;

UPDATE employees AS e
SET above_department_average = (
    SELECT CASE
        WHEN e.salary > AVG(e2.salary) THEN 1
        ELSE 0
    END
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Important: This example illustrates a correlated update, but MySQL restrictions can affect statements that read from the same table they modify. Depending on the MySQL version and query structure, the statement may be rejected because the target table is also used in a subquery. Do not assume this exact statement is portable across all MySQL versions. A derived-table approach, a temporary table, or another version-appropriate rewrite may be required. Always test data-modification queries on a safe copy before running them on production data.

13. Correlated Subqueries With DELETE

A correlated subquery can help delete records that do not have a related record in another table. For example, a staging table may contain temporary employee assignments that no longer match any department.

-- Example using separate staging and reference tables
DELETE FROM employee_staging AS es
WHERE NOT EXISTS (
    SELECT 1
    FROM departments AS d
    WHERE d.department_id = es.department_id
);

The statement removes staging records whose department IDs do not exist in the department table. Use this approach only when deletion is genuinely intended. Verify the rows first by running a corresponding SELECT query and make sure backups or transaction controls are available where appropriate.

14. Subqueries Compared With JOINs

Subqueries and joins can sometimes solve the same problem, but they express different logical approaches. A subquery defines a nested result or existence condition, while a join combines rows from related tables.

14.1 Example Using a Correlated Subquery

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

14.2 Alternative Using a JOIN With a Derived Table

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
JOIN (
    SELECT department_id,
           AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) AS dept_avg
    ON dept_avg.department_id = e.department_id
WHERE e.salary > dept_avg.avg_salary;

The derived-table version calculates department averages and joins them to employees. It may be easier to inspect when the aggregate result is also needed in the output. The correlated version can be more direct when the requirement is naturally expressed as a per-row comparison.

15. Common Errors in Correlated and Non-Correlated Subqueries

15.1 Error: Scalar Subquery Returns More Than One Row

A comparison such as = (subquery) expects the subquery to return a single value. If it returns multiple rows, MySQL reports an error.

Incorrect example:

SELECT employee_name
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_id IN (1, 2)
);

Corrected example:

SELECT employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_id IN (1, 2)
);

Use IN when the inner query can return multiple values.

15.2 Error: Missing Correlation Condition

If the requirement is to compare an employee's salary with the average salary of their department, omitting the department correlation condition changes the meaning of the query.

Incorrect for department-level comparison:

SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
);

This query compares salaries with the overall average, not the department average.

Corrected:

SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

15.3 Error: Using an Alias Incorrectly

Aliases help distinguish references to the outer table from references to the inner table. A typo or incorrect alias can cause an unknown-column error or change the query's meaning.

Best practice: Use meaningful and consistent aliases, such as e for the outer employee and e2 for the inner employee.

15.4 Error: Incorrect Use of NOT IN With NULL

If a NOT IN subquery returns a NULL value, comparisons may evaluate to UNKNOWN, causing expected rows not to be returned.

For finding records without a matching department, NOT EXISTS is often a safer pattern:

SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

15.5 Error: Assuming the Subquery Always Runs Once Per Outer Row

Correlated subqueries are logically dependent on outer-row values, but the MySQL optimizer may rewrite or optimize the query. Do not assume that the physical execution plan always repeats the inner query from scratch for every row.

Use EXPLAIN to inspect the query plan and benchmark representative data before deciding that one form is faster.

15.6 Error: Comparing Values With NULL Using Equality

SQL uses three-valued logic. An expression such as salary = NULL does not test whether salary is NULL. Use IS NULL or IS NOT NULL.

SELECT employee_name
FROM employees
WHERE manager_id IS NULL;

This query returns employees whose manager ID is missing or NULL.

16. Performance Optimization and Best Practices

16.1 Create Appropriate Indexes

Indexes can help MySQL locate matching rows more efficiently. For the correlated department-average example, an index on department_id may help find employees belonging to a department.

CREATE INDEX idx_employees_department
ON employees(department_id);

The usefulness of an index depends on table size, data distribution, other indexes, and the execution plan. An index does not guarantee that every correlated query will become faster.

16.2 Use EXPLAIN to Inspect Query Plans

EXPLAIN
SELECT e.employee_name, e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

EXPLAIN displays information about how MySQL plans to execute a query. Depending on the MySQL version and statement, EXPLAIN ANALYZE can provide actual execution information by running the query. Use it carefully because it executes the statement being analyzed.

16.3 Select Only the Required Columns

Avoid selecting unnecessary columns in the outer query. For existence checks, SELECT 1 is a common convention because only the presence of a matching row matters.

16.4 Avoid Unnecessary Nested Queries

Keep SQL readable by using the simplest query structure that correctly expresses the requirement. Some nested queries can be replaced with joins or derived tables, but rewriting is not automatically an optimization.

16.5 Match the Query to the Requirement

• Use a non-correlated subquery for a condition based on a result independent of the current outer row.
• Use a correlated subquery when the inner calculation or existence test depends on the current outer-row values.
• Use EXISTS when the question is whether a matching row exists.
• Use NOT EXISTS when the question is whether no matching row exists.
• Use a join or derived table when related columns or grouped results need to be combined with detail records.

16.6 Test With Representative Data

Test queries with small datasets first, then validate them against realistic volumes and edge cases. Include departments with no employees, departments with one employee, duplicate salary values, and NULL values where relevant.

17. Practical Project: Employee Salary Analysis

Project Objective

Create SQL queries that identify employees earning above the company average, employees earning above their department average, department-level highest earners, and departments without employees.

Task 1: Find Employees Above the Overall Average

SELECT employee_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Concept: Non-correlated subquery.

Task 2: Find Employees Above Their Department Average

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Concept: Correlated subquery.

Task 3: Find the Highest-Paid Employee in Each Department

SELECT e.employee_name,
       e.department_id,
       e.salary
FROM employees AS e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees AS e2
    WHERE e2.department_id = e.department_id
);

Concept: Correlated subquery with an aggregate function.

Task 4: Find Departments Without Employees

SELECT d.department_name
FROM departments AS d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees AS e
    WHERE e.department_id = d.department_id
);

Concept: Correlated subquery with NOT EXISTS.

Task 5: Validate the Results

1. Calculate the overall average salary independently.
2. Calculate each department's average salary using GROUP BY department_id.
3. Compare the correlated-query results with the grouped averages.
4. Check that the highest-paid employee in each department is correctly identified.
5. Verify that departments with no matching employees are returned.
6. Use EXPLAIN to review the execution plan for the larger queries.

18. Practice Exercises

1. Write a non-correlated subquery to find employees earning less than the overall average salary.
2. Write a non-correlated subquery to find employees earning the minimum salary in the company.
3. Write a correlated subquery to find employees earning less than their department's average salary.
4. Write a correlated subquery to find employees earning the minimum salary in their department.
5. Use EXISTS to find departments that have at least one employee.
6. Use NOT EXISTS to find departments without employees.
7. Find employees whose salaries are greater than every salary in the HR department by using ALL.
8. Find employees who earn more than at least one employee in the HR department by using ANY.
9. Rewrite a correlated department-average query using a grouped derived table and a join.
10. Compare the results and execution plans of the correlated and derived-table approaches.

19. Interview Questions and Answers

Q1. What is a subquery in MySQL?

Answer: A subquery is an SQL query nested inside another SQL statement. Its result is used by the outer query to retrieve, filter, compare, or modify data.

Q2. What is a non-correlated subquery?

Answer: A non-correlated subquery does not reference values from the outer query. It can be evaluated independently and its result is used by the outer query.

Q3. What is a correlated subquery?

Answer: A correlated subquery references one or more columns from the outer query. Its result depends on the values supplied by the outer-row context.

Q4. What is the main difference between correlated and non-correlated subqueries?

Answer: A non-correlated subquery is independent of the outer query's current row, whereas a correlated subquery refers to outer-query values and is logically evaluated in that context.

Q5. Can a correlated subquery use aggregate functions?

Answer: Yes. Aggregate functions such as AVG(), MAX(), MIN(), and COUNT() can calculate values for records related to the current outer row.

Q6. What is the purpose of EXISTS?

Answer: EXISTS checks whether a subquery returns at least one row. It is commonly used to test whether related records exist.

Q7. What is the purpose of NOT EXISTS?

Answer: NOT EXISTS checks whether a subquery returns no rows. It is useful for finding records without corresponding records in another table.

Q8. Can a non-correlated subquery return multiple rows?

Answer: Yes. A subquery can return multiple rows when used with operators such as IN, ANY, or ALL. A scalar comparison such as = generally requires a single-row result.

Q9. Are correlated subqueries always slower than non-correlated subqueries?

Answer: No. Performance depends on the query, indexes, data distribution, and MySQL optimizer. Some correlated queries may be transformed into more efficient execution plans. Use EXPLAIN and appropriate benchmarks to evaluate them.

Q10. Can a correlated subquery be replaced with a JOIN?

Answer: Sometimes. A join with a grouped derived table can replace a correlated aggregate query. However, the correct rewrite depends on the required output, duplicate behavior, NULL handling, and query semantics.

Q11. Why are table aliases useful in correlated subqueries?

Answer: Aliases distinguish the outer table reference from the inner table reference. They make correlation conditions easier to understand and reduce ambiguity.

Q12. How can you find departments without employees?

Answer: Use a correlated NOT EXISTS subquery that checks whether any employee has the department's ID.

20. Quick Revision Checklist

• A subquery is a query nested inside another SQL statement.
• The outer query consumes the result of the inner query.
• A non-correlated subquery does not reference outer-query values.
• A correlated subquery references one or more outer-query columns.
• Use table aliases to distinguish inner and outer references.
• Use scalar comparison operators when the subquery returns one value.
• Use IN when comparing against a set of values.
• Use EXISTS to test whether matching rows exist.
• Use NOT EXISTS to identify missing relationships.
• Use aggregate functions to calculate summary values.
• Check NULL handling, especially when using NOT IN.
• Use EXPLAIN to inspect query execution plans.
• Compare alternative query forms only after confirming that they return equivalent results.
• Test data-modification statements carefully before using them in production.

21. Conclusion

Correlated and non-correlated subqueries are important MySQL techniques for solving data retrieval and analysis problems. Non-correlated subqueries are independent of the outer query's current row and are useful for comparisons against overall averages, maximums, minimums, and fixed result sets. Correlated subqueries reference outer-query columns and are useful for row-specific comparisons, department-level calculations, and checking whether related records exist.

To use these techniques effectively, understand the relationship between the inner and outer queries, choose appropriate operators, use clear aliases, handle NULL values correctly, and test queries against realistic data. Correlated subqueries, non-correlated subqueries, joins, and derived tables each have useful applications. The best approach is the one that expresses the required logic clearly and performs well for the actual database workload.

Continue Learning: Explore the MySQL Training Course to strengthen your SQL and database skills, or register for a course demo to learn more about the training.